package com.surfacing.rcp.db.dao.mysql;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;


import org.apache.log4j.Logger;

import com.surfacing.rcp.db.DataSource;
import com.surfacing.rcp.db.Queries;
import com.surfacing.rcp.db.dao.AnforderungDAO;
import com.surfacing.rcp.db.dao.DienststelleDAO;
import com.surfacing.rcp.db.dao.DoctorDAO;
import com.surfacing.rcp.db.dao.EinsatzartDAO;
import com.surfacing.rcp.db.dao.JournalDAO;
import com.surfacing.rcp.db.dao.NacaDAO;
import com.surfacing.rcp.db.dao.NfsDAO;
import com.surfacing.rcp.db.dao.TransportzielDAO;
import com.surfacing.rcp.db.dao.factory.DaoFactory;
import com.surfacing.rcp.db.util.Util;
import com.surfacing.rcp.model.entity.Journal;

public class JournalDAOMySQL implements JournalDAO {
	private static final Logger log = Logger.getLogger(JournalDAOMySQL.class);

	// The data source to get the connection and the queries file
	private final DataSource source = DataSource.getInstance();
	private final Queries queries = Queries.getInstance();

	// Dependencies
	private final TransportzielDAO transportzielDAO = DaoFactory.MYSQL
			.createTransportzielDAO();
	private final DienststelleDAO dienststelleDAO = DaoFactory.MYSQL
			.createDienststelleDAO();
	private final EinsatzartDAO einsatzartDAO = DaoFactory.MYSQL
			.createEinsatzartDAO();
	private final AnforderungDAO anforderungDAO = DaoFactory.MYSQL
			.createAnforderungDAO();
	private final NfsDAO nfsDAO = DaoFactory.MYSQL.createNfsDAO();
	private final DoctorDAO doctorDAO = DaoFactory.MYSQL.createDoctorDAO();
	private final NacaDAO nacaDAO = DaoFactory.MYSQL.createNacaDAO();

	// INSERT INTO Journal(protokollnummer, datum, name, alarmZeit, endZeit,
	// notfallort, nachforderung, transportziel_fk, einsatzart_fk,
	// dienststelle_fk, azubi, nfs_fk, doctor_fk, km, naca) \
	// VALUES(?;?;?;?;?;?;?;?;?;?;?;?;?;?;?);

	@Override
	public boolean addJournal(Journal journal) throws SQLException {
		Connection connection = source.getConnection();
		try {
			final PreparedStatement query = connection.prepareStatement(queries
					.getStatment("insert.JournalEntry"));
			query.setString(1, journal.getProtokollnummer());
			query.setDate(2, journal.getDatum());
			query.setString(3, journal.getName());
			query.setString(4,
					Util.convertGregorianToTime(journal.getAlarmZeit()));
			query.setString(5,
					Util.convertGregorianToTime(journal.getEndZeit()));
			query.setString(6, journal.getNotfallort());
			query.setInt(7, journal.getNachforderung().getId());
			query.setInt(8, journal.getTransportziel().getId());
			query.setInt(9, journal.getEinsatzart().getId());
			query.setInt(10, journal.getDienststelle().getId());
			query.setString(11, journal.getAzubi());
			query.setInt(12, journal.getNfs().getId());
			query.setInt(13, journal.getDoctor().getId());
			query.setString(14, journal.getKm());
			query.setInt(15, journal.getNaca().getNaca());

			if (log.isDebugEnabled())
				log.debug("Updatequery: " + query.toString());
			if (query.executeUpdate() == 0)
				return false;
			// successfully
			return true;
		} finally {
			connection.close();
		}
	}

	@Override
	public List<Journal> getAllJournal() throws SQLException {
		Connection connection = source.getConnection();
		try {
			// INSERT INTO Journal(protokollnummer, datum, name, alarmZeit,
			// endZeit,
			// notfallort, nachforderung, transportziel_fk, einsatzart_fk,
			// dienststelle_fk, azubi, nfs_fk, doctor_fk, km, naca)
			final PreparedStatement query = connection.prepareStatement(queries
					.getStatment("list.JournalEntrys"));
			final ResultSet rs = query.executeQuery();
			// create the journal list and loop over the result
			List<Journal> journalMembers = new ArrayList<Journal>();
			while (rs.next()) {
				Journal journal = new Journal();
				journal.setId(rs.getInt("j.id"));
				journal.setProtokollnummer(rs.getString("j.protokollnummer"));
				journal.setDatum(rs.getDate("j.datum"));
				journal.setName(rs.getString("j.name"));
				journal.setAlarmZeit(rs.getTime("j.alarmZeit").getTime());
				journal.setEndZeit(rs.getTime("j.endZeit").getTime());
				journal.setCreated(rs.getTimestamp("j.created"));
				journal.setChanged(rs.getTimestamp("j.changed"));
				journal.setNotfallort(rs.getString("j.notfallort"));
				journal.setNachforderung(anforderungDAO.getAnforderungByID(rs
						.getInt("j.anforderung_fk")));
				journal.setTransportziel(transportzielDAO
						.getTransportzielByID(rs.getInt("j.transportziel_fk")));
				journal.setEinsatzart(einsatzartDAO.getEinsatzartByID(rs
						.getInt("j.einsatzart_fk")));
				journal.setDienststelle(dienststelleDAO.getDienststelleByID(rs
						.getInt("j.dienststelle_fk")));
				journal.setAzubi(rs.getString("j.azubi"));
				journal.setNfs(nfsDAO.getNfsByID(rs.getInt("j.nfs_fk")));
				journal.setDoctor(doctorDAO.getDoctorByID(rs
						.getInt("j.doctor_fk")));
				journal.setKm(rs.getString("j.km"));
				journal.setNaca(nacaDAO.getNacaByID(rs.getInt("j.naca")));

				journalMembers.add(journal);
			}
			return journalMembers;
		} finally {
			connection.close();
		}
	}

	@Override
	public Journal getJournalByID(int id) throws SQLException {
		Connection connection = source.getConnection();
		try {
			final PreparedStatement query = connection.prepareStatement(queries
					.getStatment("get.JournalByID"));
			query.setInt(1, id);
			final ResultSet rs = query.executeQuery();
			// assert we have a result
			if (rs.first()) {
				Journal journal = new Journal();
				journal.setId(rs.getInt("j.id"));
				journal.setProtokollnummer(rs.getString("j.protokollnummer"));
				journal.setDatum(rs.getDate("j.datum"));
				journal.setName(rs.getString("j.name"));
				journal.setAlarmZeit(rs.getTime("j.alarmZeit").getTime());
				journal.setEndZeit(rs.getTime("j.endZeit").getTime());
				journal.setCreated(rs.getTimestamp("j.created"));
				journal.setChanged(rs.getTimestamp("j.changed"));
				journal.setNotfallort(rs.getString("j.notfallort"));
				journal.setNachforderung(anforderungDAO.getAnforderungByID(rs
						.getInt("j.nachforderung_fk")));
				journal.setTransportziel(transportzielDAO
						.getTransportzielByID(rs.getInt("j.transportziel_fk")));
				journal.setEinsatzart(einsatzartDAO.getEinsatzartByID(rs
						.getInt("j.einsatzart_fk")));
				journal.setDienststelle(dienststelleDAO.getDienststelleByID(rs
						.getInt("j.dienststelle_fk")));
				journal.setAzubi(rs.getString("j.azubi"));
				journal.setNfs(nfsDAO.getNfsByID(rs.getInt("j.nfs_fk")));
				journal.setDoctor(doctorDAO.getDoctorByID(rs
						.getInt("j.doctor_fk")));
				journal.setKm(rs.getString("j.km"));
				journal.setNaca(nacaDAO.getNacaByID(rs.getInt("j.naca")));
				return journal;
			}
			// no result set
			return null;
		} finally {
			connection.close();
		}
	}

	@Override
	public boolean updateJournal(Journal journal) throws SQLException {
		Connection connection = source.getConnection();
		try {
			final PreparedStatement query = connection.prepareStatement(queries
					.getStatment("update.JournalEntry"));
			query.setString(1, journal.getProtokollnummer());
			query.setDate(2, journal.getDatum());
			query.setString(3, journal.getName());
			query.setString(4,
					Util.convertGregorianToTime(journal.getAlarmZeit()));
			query.setString(5,
					Util.convertGregorianToTime(journal.getEndZeit()));
			query.setString(6, journal.getNotfallort());
			query.setInt(7, journal.getNachforderung().getId());
			query.setInt(8, journal.getTransportziel().getId());
			query.setInt(9, journal.getEinsatzart().getId());
			query.setInt(10, journal.getDienststelle().getId());
			query.setString(11, journal.getAzubi());
			query.setInt(12, journal.getNfs().getId());
			query.setInt(13, journal.getDoctor().getId());
			query.setString(14, journal.getKm());
			query.setInt(15, journal.getNaca().getNaca());

			query.setInt(16, journal.getId());

			if (log.isDebugEnabled())
				log.debug("Updatequery: " + query.toString());
			// check if the update was successfully
			if (query.executeUpdate() == 0)
				return false;

			return true;
		} finally {
			connection.close();
		}
	}

	@Override
	public boolean deleteJournal(Journal journal) throws SQLException {
		Connection connection = source.getConnection();
		try {
			final PreparedStatement query1 = connection
					.prepareStatement(queries
							.getStatment("delete.JournalEntryByID"));
			query1.setInt(1, journal.getId());
			if (query1.executeUpdate() == 0)
				return false;
			return true;
		} finally {
			connection.close();
		}
	}

	@Override
	public List<Journal> getRangeJournal(int id) throws SQLException {
		Connection connection = source.getConnection();
		try {
			// INSERT INTO Journal(protokollnummer, datum, name, alarmZeit,
			// endZeit,
			// notfallort, nachforderung, transportziel_fk, einsatzart_fk,
			// dienststelle_fk, azubi, nfs_fk, doctor_fk, km, naca)
			final PreparedStatement query = connection.prepareStatement(queries
					.getStatment("list.JournalRangeEntrys"));
			query.setInt(1, id);
			final ResultSet rs = query.executeQuery();
			// create the journal list and loop over the result
			List<Journal> journalMembers = new ArrayList<Journal>();
			while (rs.next()) {
				Journal journal = new Journal();
				journal.setId(rs.getInt("j.id"));
				journal.setProtokollnummer(rs.getString("j.protokollnummer"));
				journal.setDatum(rs.getDate("j.datum"));
				journal.setName(rs.getString("j.name"));
				journal.setAlarmZeit(rs.getTime("j.alarmZeit").getTime());
				journal.setEndZeit(rs.getTime("j.endZeit").getTime());
				journal.setCreated(rs.getTimestamp("j.created"));
				journal.setChanged(rs.getTimestamp("j.changed"));
				journal.setNotfallort(rs.getString("j.notfallort"));
				journal.setNachforderung(anforderungDAO.getAnforderungByID(rs
						.getInt("j.anforderung_fk")));
				journal.setTransportziel(transportzielDAO
						.getTransportzielByID(rs.getInt("j.transportziel_fk")));
				journal.setEinsatzart(einsatzartDAO.getEinsatzartByID(rs
						.getInt("j.einsatzart_fk")));
				journal.setDienststelle(dienststelleDAO.getDienststelleByID(rs
						.getInt("j.dienststelle_fk")));
				journal.setAzubi(rs.getString("j.azubi"));
				journal.setNfs(nfsDAO.getNfsByID(rs.getInt("j.nfs_fk")));
				journal.setDoctor(doctorDAO.getDoctorByID(rs
						.getInt("j.doctor_fk")));
				journal.setKm(rs.getString("j.km"));
				journal.setNaca(nacaDAO.getNacaByID(rs.getInt("j.naca")));

				journalMembers.add(journal);
			}
			return journalMembers;
		} finally {
			connection.close();
		}
	}

}
